package database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;


/** Helper to the database, manages versions and creation */

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "homeworkplanner.db";
	private static final int DATABASE_VERSION = 5;

	// table names
	public static final String COURSETABLE = "courses";
	public static final String ASSIGNMENTTABLE = "assignments";
	public static final String GRADETABLE = "grades";
	public static final String REMINDERTABLE = "reminders";

	//course table
	public static final String COURSENAME = "name";
	public static final String COURSEMONDAY = "monday";
	public static final String COURSETUESDAY = "tuesday";
	public static final String COURSEWEDNESDAY = "wednesday";
	public static final String COURSETHURSDAY = "thursday";
	public static final String COURSEFRIDAY = "friday";
	public static final String COURSESATURDAY = "saturday";
	public static final String COURSESUNDAY = "sunday";
	public static final String COURSETIME = "time";
	public static final String COURSECREDITS = "coursecredits";
	public static final String COURSEENDTIME = "courseendtime";
	public static final String COURSELOCATION = "courselocation";

	//assignment table
	public static final String ASSIGNMENTNAME = "name";
	public static final String COURSEID = "courseid";
	public static final String ASSIGNMENTDESCRIPTION = "assignmentdescription";
	public static final String ASSIGNMENTDUEDATE = "assignmentduedate";
	public static final String ASSIGNMENTCOMPLETED = "assignmentcompleted";

	//grade table
	public static final String GRADENAME = "gradename";
	public static final String ASSIGNMENTID = "assignmentid";
	public static final String POINTSEARNED = "pointsearned";
	public static final String POINTSOUTOF = "pointsoutof";
	
	//notifications table
	public static final String REMINDERINTEGER = "notifyinteger";
	public static final String REMINDERUNITS = "notifyunits";
	

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table " + COURSETABLE + "( " + BaseColumns._ID
		+ " integer primary key autoincrement, " 
		+ COURSENAME + " text not null, "
		+ COURSEMONDAY + " integer, "
		+ COURSETUESDAY + " integer, "
		+ COURSEWEDNESDAY + " integer, "
		+ COURSETHURSDAY + " integer, "
		+ COURSEFRIDAY + " integer, "
		+ COURSESATURDAY + " integer, "
		+ COURSESUNDAY + " integer, "
		+ COURSETIME + " integer, "
		+ COURSECREDITS + " integer, " 
		+ COURSEENDTIME + " integer, "
		+ COURSELOCATION + " text );";
		Log.d("EventsData", "onCreate: " + sql); //for debugging purposes
		db.execSQL(sql);

		sql = "create table " + ASSIGNMENTTABLE + "( " + BaseColumns._ID
		+ " integer primary key autoincrement, " 
		+ ASSIGNMENTNAME + " text not null, "
		+ ASSIGNMENTDESCRIPTION + " text not null, "
		+ COURSEID + " integer not null, "
		+ ASSIGNMENTDUEDATE + " integer, "
		+ ASSIGNMENTCOMPLETED + " integer, "
		+ "FOREIGN KEY("+COURSEID+") REFERENCES "+COURSETABLE+"(_id));";
		//one course can have many assignments

		Log.d("EventsData", "onCreate: " + sql);
		db.execSQL(sql);

		sql = "create table " + GRADETABLE + "( " + BaseColumns._ID
		+ " integer primary key autoincrement, " 
		+ GRADENAME + " text not null, "
		+ COURSEID + " integer not null, "
		+ POINTSEARNED + " real, "
		+ POINTSOUTOF + " real, "
		+ ASSIGNMENTID + " integer, "
		+ "FOREIGN KEY("+COURSEID+") REFERENCES "+COURSETABLE+"(_id));";
		//one course can have many grades
		Log.d("EventsData", "onCreate: " + sql);
		db.execSQL(sql);
		
		
		sql = "create table " + REMINDERTABLE + "( " + BaseColumns._ID
		+ " integer primary key autoincrement, " 
		+ ASSIGNMENTID + " integer, "
		+ REMINDERINTEGER + " integer, "
		+ REMINDERUNITS + " text not null, "
		+ "FOREIGN KEY("+ASSIGNMENTID+") REFERENCES "+ASSIGNMENTTABLE+"(_id));";
		//one assignment can have reminders set several times
		Log.d("EventsData", "onCreate: " + sql);
		db.execSQL(sql);

	}

	//documents the updates made in building this application and its different database table versions
	//as such
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		
		if (oldVersion < 2) {
			sql = "alter table " + GRADETABLE +" ADD COLUMN "+ASSIGNMENTID+";";
			db.execSQL(sql);
			sql = "alter table " + COURSETABLE +" ADD COLUMN "+COURSECREDITS+";";
			db.execSQL(sql);
			Log.d("EventsData", "onUpgrade	: " + sql);
		}
		
		if(oldVersion < 3){
			
			sql = "create table " + REMINDERTABLE + "( " + BaseColumns._ID
			+ " integer primary key autoincrement, " 
			+ ASSIGNMENTID + " integer, "
			+ REMINDERINTEGER + " integer, "
			+ REMINDERUNITS + " text not null, "
			+ "FOREIGN KEY("+ASSIGNMENTID+") REFERENCES "+ASSIGNMENTTABLE+"(_id));";

			Log.d("EventsData", "onCreate: " + sql);
			db.execSQL(sql);
			
		}
		if( oldVersion < 4)
		{
			sql = "alter table " + ASSIGNMENTTABLE +" ADD COLUMN "+ASSIGNMENTCOMPLETED+ " INTEGER DEFAULT '0';";
			db.execSQL(sql);
		}
		
		if( oldVersion < 5 )
		{
			sql = "alter table " + COURSETABLE +" ADD COLUMN "+COURSEENDTIME+ " INTEGER;";
			db.execSQL(sql);
			
			sql = "alter table " + COURSETABLE +" ADD COLUMN "+COURSELOCATION+ " TEXT;";
			db.execSQL(sql);
		}
	}
	

}